﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DB.SqlHelper;
using Framework.Common.Common;
using Mode;

namespace Framework.DataAccess
{
    // 导出考场成绩
    public class ExportExam
    {
        private int examID = -1;
        private DataTable result_list = new DataTable();
        private DataTable result_summary = new DataTable("考场成绩汇总表");

        private DbInstance db = DbManager.GetDbInstance();

        public ExportExam() { }
        public ExportExam(int exam)
        {
            examID = exam;

            result_summary.Columns.Add("考生姓名", typeof(string));
            result_summary.Columns.Add("考场名称", typeof(string));
            result_summary.Columns.Add("单选题得分", typeof(int));
            result_summary.Columns.Add("多选题得分", typeof(int));
            result_summary.Columns.Add("判断题得分", typeof(int));
            result_summary.Columns.Add("填空题得分", typeof(int));
            result_summary.Columns.Add("问答题得分", typeof(int));
            result_summary.Columns.Add("打字题得分", typeof(int));
            result_summary.Columns.Add("总得分", typeof(int));
        }

        public DataSet ExportExamDataSet()
        {
            DataSet result_ds = new DataSet();

            get_exam_list();
            result_ds.Tables.Add(result_list.Copy());
            result_list.Dispose();

            get_exam_summary();
            result_ds.Tables.Add(result_summary.Copy());
            result_summary.Dispose();

            return result_ds;
        }


        private void get_exam_list()
        {
            result_list =  db.ExecuteDataTableBySql("select * from export_exam_candidate where ExamID=" + examID);
            result_list.TableName = "考场考生表";
            result_list.Columns.Remove("ExamID");
        }

        private void get_exam_summary()
        {
            DataTable temp_dt = db.ExecuteDataTableBySql("select C_ID, MemberName, ExamName from candidate_view where ExamID =" + examID);
            foreach (DataRow row in temp_dt.Rows)
            {
                DataRow dr = result_summary.NewRow();
                dr["考生姓名"] = row["MemberName"];
                dr["考场名称"] = row["ExamName"];

                int []score = new int[8]{0,0,0,0,0,0,0,0};
                DataTable sdt = db.ExecuteDataTableBySql("select * from export_exam_summary where candidate =" + row["C_ID"].ToInt());
                foreach (DataRow ss in sdt.Rows)
                {
                    int type = ss["topictype"].ToInt();
                    score[type] += ss["sroce"].ToInt();
                }
                sdt.Dispose();

                dr["单选题得分"] = score[1];
                dr["多选题得分"] = score[2];
                dr["判断题得分"] = score[3];
                dr["填空题得分"] = score[4];
                dr["问答题得分"] = score[5];
                dr["打字题得分"] = score[6];
                dr["总得分"] = score.Sum();

                result_summary.Rows.Add(dr);
            }

            temp_dt.Dispose();
        }


        public DataTable ExportExamListGetExamList(int areID, int modelID, int status, string name = "", DateTime? beginDate = null, DateTime? endDate = null)
        {
            string strWhere = " Where 1=1";
            strWhere += (areID > -1 ? string.Format(" And model in(Select M_ID From are_role_model_view Where A_ID={0})", areID) : "");
            strWhere += (modelID>-1 ?  " And model=" + modelID:"");
            strWhere += (status > -1 ? " And E_Status=" + status : "");
            strWhere += (name.Length > 0 ? string.Format(" And E_Name Like '%{0}%'", name.Replace("'", "''")) : "");
            strWhere += (beginDate.HasValue? string.Format(" And E_BeginDate>='{0}'", beginDate.Value.ToString("yyyy-MM-dd")):"");
            strWhere += (endDate.HasValue ? string.Format(" And E_EndDate<='{0}'", endDate.Value.ToString("yyyy-MM-dd")) : "");

            string sql = "SELECT model,  E_Name as '考场名称', ";
            sql += "'' as '参考职务', ";
            sql += "CONVERT(varchar(16),[E_BeginDate],120)As '开考时间',  ";
            sql += "CONVERT(varchar(16),[E_EndDate],120)As '截考时间', ";
            sql += "cast(E_Time as varchar(10)) +' 分钟' as  '考试时长', ";
            sql += "cast((Select COUNT(*) From candidate_table Where examination=A.E_ID) as varchar(10)) +' 人' as  '应考人数', ";
            sql += "cast((Select COUNT(*) From candidate_table Where examination=A.E_ID And [status]>-2) as varchar(10)) + ' 人' As '实考人数' , ";
            sql += "CONVERT(varchar(16),E_Date,120)As '考场创建日期', ";
            sql += "case(E_Status) when 0 then '启用' else '停用' end as '考场状态' ";
            sql += "From examination_view As A  {0}";

            sql = string.Format(sql, strWhere);

            DbCommand cmd = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(cmd);


            ModeAdpater myModel = new ModeAdpater();
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                int model = dt.Rows[index]["model"].ToInt();
                dt.Rows[index]["参考职务"] = myModel.GetModelRoleName(model).Replace("&nbsp;", "");
            }

            dt.Columns.Remove("model");
            return dt;
        }


    }


}
